package per.vic.attachment.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import per.vic.attachment.model.Attachment;

/**
 * @description: 附件表DAO 不用MYBATIS 是为了减少额外的配置,也方便跨数据库
 * @author: Vic.xu
 * @date: 2019年12月11日 上午10:01:37
 */
@Repository
public class AttachmentDao {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	String deleteSql = "delete from attachment where id=?";
	
	String columns = "a.`id` AS `id` ," + " a.`absolute_path` AS `absolutePath` ,"
			+ " a.`relative_path` AS `relativePath` ," + " a.`temporary` AS `temporary` ," + " a.`module` AS `module` ,"
			+ " a.`content_type` AS `contentType` ," + " a.`size` AS `size` ,\n" + " a.`filename` AS `filename` ,"
			+ " a.`original_name` AS `originalName` ," + " a.`create_time` AS `createTime` ";

	String tableName = "attachment";
	/**
	 * 判断附件表是否存在,放弃手动建表
	 */
	public boolean isAttachmentTableExist() {
		ResultSet rs = null;
		try(Connection conn = jdbcTemplate.getDataSource().getConnection();) {
			rs = null;
			DatabaseMetaData data = conn.getMetaData();
			String[] types = {"TABLE"};
			rs = data.getTables(null, null, tableName, types);
			if(rs.next()){
				return true;
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		return false;
	}
	
	/**
	 * 新增附件
	 */
	public Attachment insert(Attachment attachment) {
		String sql = "insert into attachment(absolute_path, relative_path, `temporary`, module,content_type, size, filename, original_name,create_time) values (?, ?, 1, ?, ?, ?, ?, ?, now())";
		KeyHolder keyHolder = new GeneratedKeyHolder();

		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, attachment.getAbsolutePath());
				ps.setString(2, attachment.getRelativePath());
				ps.setString(3, attachment.getModule());
				ps.setString(4, attachment.getContentType());
				ps.setInt(5, attachment.getSize());
				ps.setString(6, attachment.getFilename());
				ps.setString(7, attachment.getOriginalName());
				return ps;
			}
		}, keyHolder);
		attachment.setId(keyHolder.getKey().intValue());
		return attachment;
	}

	/**
	 * 修改附件状态
	 * 
	 * @param id
	 * @param temporary 是否是否临时态
	 */
	public void updateTemporary(int id, boolean temporary) {
		jdbcTemplate.update("update attachment set `temporary`=? where id=?", temporary, id);
	}

	/**
	 * 修改附件状态
	 * 
	 * @param ids:      形如1,2,3
	 * @param temporary
	 */
	public void updateTemporary(String ids, boolean temporary) {
		jdbcTemplate.update("update attachment set `temporary`=? where id in (" + ids + ")", temporary);
	}


	/**
	 * 根据id查询附件
	 */
	public Attachment selectAttachmentById(Integer id) {
		String sql = "select " + columns + " FROM attachment a  where a.id = ?";
		try {
			Attachment attachment = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Attachment>(Attachment.class), id);
			return attachment;

		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	/**
	 * 查询出全部的临时状态的附件
	 */
	public List<Attachment> selectTemporaryAttachments() {
		String sql = "select " + columns + " FROM attachment a where a.temporary = 1";
		List<Attachment> list = jdbcTemplate.query(sql,  new BeanPropertyRowMapper<Attachment>(Attachment.class));
		return list;
	}


	/**
	 * 删除附件
	 */
	public void delete(Integer id) {
		jdbcTemplate.update(deleteSql, id);
	}

	/**
	 * 批量删除附件
	 * 数据库连接需要配置:rewriteBatchedStatements=true 不然还是逐条执行
	 */
	public void delete(List<Integer> ids) {
		jdbcTemplate.batchUpdate(deleteSql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, ids.get(i));
			}
			@Override
			public int getBatchSize() {
				return ids.size();
			}
		});
	}
}
